﻿using API_Service.BizLogic.BasicData;
using API_Service.BizLogic.PartNo;
using API_Service.BizLogic.WipLogApi;
using API_Service.DBHelper;
using Dapper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.WIPControl
{
    public class WIPKeypartsService : BaseLogicService
    {
        private BomDataService _bomService = new BomDataService();
        private WIPTrackingService _wiptrackService = new WIPTrackingService();
        private SNControlService _snCtrlService = new SNControlService();
        private PartNoService _kpnService = new PartNoService();
        public ExecutionResult<dynamic> ValidateWIPKPSExist(dynamic vModel)
        {
            var result = new ExecutionResult<dynamic>();
            string vSN = vModel.sn, vKPN = vModel.kpn, vKP_SN = vModel.kpsn;
            if (!vKP_SN.Equals("N/A") && !vKP_SN.Equals("END"))
            {
                result = this.QuerySNWIPKeypartsInfo(null, null, vKP_SN);
                if (result.Status)
                {
                    if (result.Datas.Length > 0 && !result.Datas[0].serial_number.Equals(vSN))
                    {
                        result.Status = false;
                        result.Message = "此序号[" + vKP_SN + "]已被使用.";
                        return result;
                    }
                }
            }
            result = this.QuerySNWIPKeypartsInfo(vSN, vKPN, vKP_SN);
            if (result.Status)
            {
                if (result.Datas.Length > 0)
                {
                    result.Status = false;
                    result.Message = "料号[" + vKPN + "]或序号[" + vKP_SN + "]已存在SN[" + vSN + "]WIP捡料记录表，请确认。";
                }
                else
                {
                    var _insertFlag = 0;
                    result = this.QuerySNWIPKeypartsInfo(vSN, vKPN);
                    if (result.Status && result.Datas.Length>0)
                    {
                        _insertFlag = 1;
                    }                    
                    result.Status = true;
                    result.Message = "料号[" + vKPN + "]或序号[" + vKP_SN + "]不存在WIP捡料记录表。";
                    result.ExecuteCount = _insertFlag;
                }
            }


            return result;
        }

        public ExecutionResult<dynamic> GetSNWIPKPSList(string vSN)
        {
            var result = new ExecutionResult<dynamic>();

            if (!String.IsNullOrEmpty(vSN))
            {
                result = this.QuerySNWIPKeypartsInfo(vSN);
                if (result.Status)
                {
                    if (result.Datas.Length < 1)
                    {
                        result.Status = false;
                        result.Message = "SN[" + vSN + "]序号错误或者无捡料记录";
                    }
                }
            }
            else
            {
                result.Status = false;
                result.Message = "SN不能为空。";
            }

            return result;
        }

        private ExecutionResult<dynamic> QuerySNWIPKeypartsInfo(string vSN=null, string vKPN = null,string vKP_SN =null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                conn.Open();
                try
                {
                    string sql = "SELECT * FROM [dbo].[r_wip_keyparts_t] WHERE 1=1";
                    if (!string.IsNullOrEmpty(vSN))
                    {
                        sql = sql + " AND serial_number = @sn ";
                    }
                    if (!String.IsNullOrEmpty(vKPN))
                    {
                        sql = sql + " AND key_part_no = @kpn ";
                    }
                    if (!String.IsNullOrEmpty(vKP_SN))
                    {
                        sql = sql + " AND key_part_sn = @kpsn";
                    }

                    result.Datas = conn.Query(sql, new { sn = vSN, kpn = vKPN, kpsn = vKP_SN }).ToArray();
                    result.Status = true;
                    result.Message = "Query SN wip keyparts info success.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        public ExecutionResult<dynamic> QueryWipKpsListInfo(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            var model = JsonConvert.DeserializeObject<dynamic>(viewModel);
            string vSN = model.serial_number, vKPN = model.key_part_no, vKP_SN = model.key_part_sn;

            result = QuerySNWIPKeypartsInfo(vSN, vKPN, vKP_SN);

            return result;
        }

        public ExecutionResult<dynamic> PickUpSNWIPKeyparts(dynamic vModel)
        {
            var result = new ExecutionResult<dynamic>();

            //dynamic vWipInfo = JsonConvert.DeserializeObject<dynamic>(vModel);
            string _sn = vModel.sn, _kpsn = vModel.kpsn, _line = vModel.line, _group = vModel.group, _emp = vModel.emp;
            Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "--" + vModel.ToString());
            if (string.IsNullOrEmpty((string)_sn))
            {
                result.Status = false;
                result.Message = "SN不能为空..";
                return result;
            }
            //check sn
            result = _wiptrackService.CheckSNExists(_sn);
            if (!result.Status)
            {
                return result;
            }
            string _strBomNo = (string)result.Datas[0].bom_no;
            //check PN
            result = this._kpnService.GetPartNoByCode(_kpsn);
            if (!result.Status)
            {
                return result;
            }
            string _kpn = result.Datas[0].key_part_no;
            if (result.Message.Equals("KPN"))
            {
                _kpsn = "N/A";
            }
            //检查bom
            result = this._bomService.CheckKPSInBOMList(_strBomNo, _kpn,_group);
            if(!result.Status)
            {
                return result;
            }
            //check alternative flag
           var _alternative = "N";
            if (!String.IsNullOrEmpty(result.Datas[0].alternative_kpn) &&
                _kpn.Equals((string)result.Datas[0].alternative_kpn))
            {
                _alternative = "Y";
            }
            //检查序号 料号是否使用
            result = ValidateWIPKPSExist(new { sn = _sn, kpn = _kpn, kpsn = _kpsn });
            if (!result.Status)
            {
                return result;
            }

         var wipKPSInfo =   new
         {
             sn = _sn,
             line = _line,
             group = _group,
             kpsn = _kpsn,
             kpn = _kpn,
             alternative = _alternative,
             qty = 1,
             emp = _emp,
             remarks = _emp
         };
            List<string> lstKPN = new List<string>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

              var dbTrans =  conn.BeginTransaction();
                try
                {
                    //更新wip kps
                    if(result.ExecuteCount == 0)
                    {
                        result = this.InsertWIPKeypartsInfo(wipKPSInfo, conn, dbTrans);
                    }
                    else
                    {
                        result = this.UpdateWipKeypartsInfo(wipKPSInfo, conn, dbTrans);
                    }
                    if (result.ExecuteCount > 0)
                    {
                        dbTrans.Commit();
                        result.Status = true;
                        result.Message = "Pick WIP keyparts success.";
                        lstKPN.Add(_kpn);
                        result.Datas = lstKPN.ToArray();
                    }
                    else
                    {
                        dbTrans.Rollback();
                    }
                }
                catch (Exception ex)
                {
                    dbTrans.Rollback();

                    result.Status = false;
                    result.Message = ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {                    
                    conn.Close();
                }
            }

            return result;
        }

        private ExecutionResult<dynamic> InsertWIPKeypartsInfo(dynamic vModel,IDbConnection vConn, IDbTransaction vTanns) 
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            #region sql and parameter
            string sql = @"INSERT INTO [dbo].[r_wip_keyparts_t]
           ([serial_number],[line_name],[group_name],[key_part_no] ,[key_part_sn] ,[pick_up_time]
           ,[qty] ,[alternative],[is_valid] ,[remarks],[create_time] ,[create_user])
     VALUES (@serial_number,@line_name,@group_name,@key_part_no, @key_part_sn,
		   GETDATE(),@qty,@alternative, 'Y',@remarks,GETDATE(),@create_user)";
            DynamicParameters dbParams = new DynamicParameters();
            dbParams.Add("@serial_number", (string)vModel.sn, DbType.String, ParameterDirection.Input);
            dbParams.Add("@key_part_no", (string)vModel.kpn, DbType.String, ParameterDirection.Input);
            dbParams.Add("@key_part_sn", (string)vModel.kpsn, DbType.String, ParameterDirection.Input);
            dbParams.Add("@line_name", (string)vModel.line, DbType.String, ParameterDirection.Input);
            dbParams.Add("@group_name", (string)vModel.group, DbType.String, ParameterDirection.Input);
            dbParams.Add("@qty", (Int32)vModel.qty, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@alternative", (string)vModel.alternative, DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", (string)vModel.remarks, DbType.String, ParameterDirection.Input);
            dbParams.Add("@create_user", (string)vModel.emp, DbType.String, ParameterDirection.Input);
            #endregion
            try
            {
                if (vConn.State != ConnectionState.Open)
                {
                    vConn.Open();
                }
                result.ExecuteCount = vConn.Execute(sql, dbParams, vTanns);
                if (result.ExecuteCount > 0)
                {
                    result.Status = true;
                    result.Message = "Insert WIP KPS success.";
                }
                else
                {
                    result.Status = false;
                    result.Message = "Insert WIP KPS Fail.";
                }
                Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "--" + result.Message);
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = "Update WIP KPS info exception. Msg:" + ex.Message;
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
            }


            return result;

        }

        private ExecutionResult<dynamic> UpdateWipKeypartsInfo(dynamic vModel, IDbConnection vConn, IDbTransaction vTanns)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            #region sql and parameters
            string sql = @"UPDATE [dbo].[r_wip_keyparts_t]
                                       SET [line_name]=@line_name,
                                           [group_name]=@group_name,
                                           [key_part_sn] =@key_part_sn 
                                          ,[pick_up_time] = GETDATE()
                                          ,[qty] = @qty
                                          ,[alternative] = @alternative
                                          ,[is_valid] = @is_valid
                                          ,[remarks] = @remarks
                                          ,[update_time] = GETDATE()
                                          ,[update_user] = @update_user
                                     WHERE [serial_number] =@serial_number
                                     AND [key_part_no] =@key_part_no";
            DynamicParameters dbParams = new DynamicParameters();
            dbParams.Add("@line_name", (string)vModel.line, DbType.String, ParameterDirection.Input);
            dbParams.Add("@group_name", (string)vModel.group, DbType.String, ParameterDirection.Input);
            dbParams.Add("@key_part_sn", (string)vModel.kpsn, DbType.String, ParameterDirection.Input);
            dbParams.Add("@qty", (Int32)vModel.qty, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@alternative", (string)vModel.alternative, DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid", (string)vModel.is_valid, DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", (string)vModel.remarks, DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_user", (string)vModel.emp, DbType.String, ParameterDirection.Input);
            dbParams.Add("@serial_number", (string)vModel.sn, DbType.String, ParameterDirection.Input);
            dbParams.Add("@key_part_no", (string)vModel.kpn, DbType.String, ParameterDirection.Input);
            #endregion
            try
            {
                if (vConn.State != ConnectionState.Open)
                {
                    vConn.Open();
                }
                result.ExecuteCount = vConn.Execute(sql, dbParams, vTanns);
                if (result.ExecuteCount > 0)
                {
                    result.Status = true;
                    result.Message = "Update WIP KPS success.";
                }
                else
                {
                    result.Status = false;
                    result.Message = "Update WIP KPS Fail.";
                }
            }
            catch (Exception ex)
            {
                result.Status = false;
                result.Message = "Update WIP KPS info exception. Msg:" + ex.Message;
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
            }

            return result;
        }

    }
}